package com.qkjia.zqkspringshell.mysql.utils;

import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Configuration;
import org.springframework.shell.standard.ShellComponent;

import javax.xml.ws.Response;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static org.springframework.jdbc.support.JdbcUtils.closeConnection;

@Slf4j
@Configuration
@ShellComponent
public class DBMysqlUtil {
    private Connection conn = null;
    private PreparedStatement ps = null;
    private Statement statement = null;
    private ResultSet rs = null;
    private String dbDriver = null;
    private String dbConnectionURL = null;
    private String dbUsername = null;
    private String dbPassword = null;
    private static final String SQL = "SELECT * FROM ";// 数据库操作



    public DBMysqlUtil() {
        this.dbDriver = null;
        this.dbConnectionURL = null;
        this.dbUsername = null;
        this.dbPassword = null;
    }

    public DBMysqlUtil(String dbDriver, String dbConnectionURL, String dbUsername, String dbPassword) {
        this.dbDriver = dbDriver;
        this.dbConnectionURL = dbConnectionURL;
        this.dbUsername = dbUsername;
        this.dbPassword = dbPassword;
    }


    /**
     * 功能：获取数据库连接
     */
    public Connection getConnection() {
        System.out.println("连接地址：" + dbConnectionURL);
//        System.out.println("用户名：" + dbUsername);
//        System.out.println("密码：" + dbPassword);
        try {
            Class.forName(dbDriver.toString());
            conn = DriverManager.getConnection(dbConnectionURL, dbUsername,
                    dbPassword);
            if (dbConnectionURL != null && dbDriver != null && dbUsername != null && dbPassword != null) {
                log.info("数据库连接成功");
            } else {
                log.info("请先连接数据！");
                return null;
            }
        } catch (Exception e) {
            log.error("Error: DbUtil.getConnection() 获得数据库链接失败.\r\n链接类型:"
                    + dbDriver + "\r\n链接URL:" + dbConnectionURL + "\r\n链接用户:"
                    + dbUsername + "\r\n链接密码:" + dbPassword, e + "请检查数据库连接");
        }
        return conn;
    }

    /**
     * 功能：执行查询语句
     */
    public ResultSet select(String sql) {
        log.info("Exec select sql:" + sql);
        try {
            conn = getConnection();
            statement = conn.createStatement();
//				ps = conn.prepareStatement(sql);
            rs = statement.executeQuery(sql);
        } catch (Exception e) {
            log.error("查询数据异常:" + e.getMessage());
        }
        return rs;

    }


    //-------------------------------

    /**
     * 功能：执行查询语句，获取记录数
     */
    public Object getRecordCount(String sql) {
        log.info("Exec getRecordCount sql:" + sql);
        int counter = 0;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if (conn == null) {
                return "请先连接数据库";
            }
            rs = ps.executeQuery(sql);
            while (rs.next()) {
                counter++;
            }
        } catch (SQLException e) {
            log.error("执行DbUtil.getRecordCount()方法发生异常，异常信息：", e);
        } finally {
            close();
        }
        System.out.println("counter总数：" + counter);
        return counter;
    }

    /**
     * 功能:针对单条记录执行更新操作(新增、修改、删除)
     */
    public int executeupdate(String sql) throws Exception {
        log.info("Exec update sql:" + sql);
        int num = 0;
        try {
            conn = getConnection();
            if (conn == null) {
                log.info("请连接数据库");
                return 0;
            }
            ps = conn.prepareStatement(sql);
            num = ps.executeUpdate();
        } catch (SQLException sqle) {
            log.error("insert/update/delete  data Exception: " +
                    sqle.getMessage());
        } finally {
            close();
        }
        System.out.println("影响条数：" + num);
        return num;
    }

    /**
     * 功能:批量执行SQL(update或delete)
     *
     * @param sqlList sql语句集合
     */
    public int executeBatch(List<String> sqlList) {
        int result = 0;
        for (String sql : sqlList) {
            try {
                result += executeupdate(sql);
            } catch (Exception e) {
                System.out.println("查询异常：" + e.getMessage());
            }
        }
        System.out.println("executeBatch Result:" + result);
        return result;
    }

    /**
     * 所有增删改
     */
    public String createdatabase(String sql) {
        log.info("Exec select sql:" + sql);
        try {
            conn = getConnection();
            statement = conn.createStatement();
            boolean execute = statement.execute(sql);
        } catch (Exception e) {
            //：如果你的SQL 语句是诸如update,insert的更新语句，应该用statement的execute()方法，
            // 如果用的是statement的executeQuery()就会出现上诉问题
            log.error("查询数据异常:" + e.getMessage());
        }
        return "没毛病";
    }

    /**
     * 所有查询操作
     */
    public ResultSet allSelect(String sql) {
        log.info("Exec select sql:" + sql);
        try {
            conn = getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            int size = rsMetaData.getColumnCount();
            //数据库字段名
            List<String> cloumnList = new ArrayList<>();
            for (int i = 0; i < size; i++) {
                cloumnList.add(rsMetaData.getColumnName(i + 1));
            }
            System.out.println(cloumnList);
            while (rs.next()) {
                for (int i = 0; i < size; i++) {
                    System.out.print(rs.getString(i+1)+"\t");
                }
                System.out.println();
            }
        } catch (Exception e) {
            //：如果你的SQL 语句是诸如update,insert的更新语句，应该用statement的execute()方法，
            // 如果用的是statement的executeQuery()就会出现上诉问题
            log.error("查询数据异常:" + e.getMessage());
        }
        return rs;
    }

    /**
     * 查询建表语句
     */
    public ResultSet createTableDDl(String TableName) {
        log.info("Exec select sql:" + TableName);
        try {
            conn = getConnection();
            statement = conn.createStatement();
            String sql = "show create table " + TableName;
            rs = statement.executeQuery(sql);
            String createSQL = null;
            System.out.println("以下是建表语句" + "/n");
            while (rs.next()) {
                BufferedReader br = new BufferedReader(new InputStreamReader(rs.getAsciiStream(rs.getMetaData().getColumnCount()), new String("UTF-8")));
                while ((createSQL = br.readLine()) != null) {
                    System.out.println(createSQL);
                }
            }
            System.out.println(rs);
        } catch (Exception e) {
            //：如果你的SQL 语句是诸如update,insert的更新语句，应该用statement的execute()方法，
            // 如果用的是statement的executeQuery()就会出现上诉问题
            log.error("查询数据异常:" + e.getMessage());
        }
        return rs;
    }


    /**
     * 查看表中所有字段
     * @return
     */
    /**
     * 获取表中所有字段名称
     *
     * @param tableName 表名
     * @return
     */
    public List<String> getColumnNames(String tableName) {
        List<String> columnNames = new ArrayList<>();
        //与数据库的连接
        conn = getConnection();
        //		statement =conn.createStatement();
        Connection conn = getConnection();
        String tableSql = SQL + tableName;
        try {
            ps = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = ps.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
            System.out.println("下面为表的所有字段");
            System.out.println(columnNames);
        } catch (SQLException e) {
            log.error("getColumnNames failure", e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    log.error("getColumnNames close pstem and connection failure", e);
                }
            }
        }
        return columnNames;
    }

    /**
     * 功能:关闭数据库的连接
     */
    public void close() {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
            log.info("关闭数据库连接成功");
        } catch (Exception e) {
            log.error("执行DbUtil.close()方法发生异常，异常信息：", e);
        }
    }

    /**
     * 查看数据库版本
     */
    public ResultSet mysqlVersionAndTime() {
        try {
            conn = getConnection();
            statement = conn.createStatement();
            String sql = "select version() ";
            rs = statement.executeQuery(sql);
            String createSQL = null;
            System.out.println("以下是数据库版本和当前时间");
            //getBinaryStream
            while (rs.next()) {
                BufferedReader br = new BufferedReader(new InputStreamReader(rs.getAsciiStream(rs.getMetaData().getColumnCount()), new String("GBK")));
                while ((createSQL = br.readLine()) != null) {
                    System.out.println(createSQL);
                }
            }
        } catch (Exception e) {
            //：如果你的SQL 语句是诸如update,insert的更新语句，应该用statement的execute()方法，
            // 如果用的是statement的executeQuery()就会出现上诉问题
            log.error("查询数据异常:" + e.getMessage());
        }
        return rs;
    }

    /**
     * 获取表中所有字段类型
     *
     * @param tableName
     * @return
     */
    public List<String> getColumnTypes(String tableName) {
        List<String> columnTypes = new ArrayList<>();
        //与数据库的连接
        conn = getConnection();
        String tableSql = SQL + tableName;
        try {
            ps = conn.prepareStatement(tableSql);
            //结果集元数据
            ResultSetMetaData rsmd = ps.getMetaData();
            //表列数
            int size = rsmd.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnTypes.add(rsmd.getColumnTypeName(i + 1));
            }
            System.out.println("获取表中所有字段类型" + columnTypes);
        } catch (SQLException e) {
            log.error("getColumnTypes failure", e);
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    log.error("getColumnTypes close pstem and connection failure", e);
                }
            }
        }
        return columnTypes;
    }



    /**
     * 获取表中字段的所有注释
     *
     * @param tableName
     * @return
     */
    public List<String> getColumnComments(String tableName) {
        //与数据库的连接
        conn = getConnection();
        //列名注释集合
        List<String> columnComments = new ArrayList<>();
        String tableSql = SQL + tableName;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(tableSql);
            rs = ps.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
                columnComments.add(rs.getString("Comment"));
            }
            System.out.println("这些是字段的注释 :" + columnComments);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    log.error("getColumnComments close ResultSet and connection failure", e);
                }
            }
        }
        return columnComments;
    }



    /**
     * 查询索引
     */
    /**
     * 获得一个表的索引信息
     */
    public void getIndexInfo(String tableName) {
        try {
            List<String> columnNames = new ArrayList<>();
            List<String> columnNames2 = new ArrayList<>();
            conn = getConnection();
            statement = conn.createStatement();
            String sql = "SHOW INDEX FROM  " + tableName;
            rs = statement.executeQuery(sql);
            String createSQL = null;
            System.out.println("以下是表的索引信息" + "/n");
            ResultSetMetaData rsMetaData = rs.getMetaData();
            int size = rsMetaData.getColumnCount();
            for (int i = 0; i < size; i++) {
                columnNames.add(rsMetaData.getColumnName(i + 1));
            }
            System.out.println(columnNames);
            while (rs.next()) {
                for (int i = 0; i < size; i++) {
//                    System.out.print(rs.getString(i+1)+"\t");
                    columnNames2.add(rs.getString(i + 1));
                }
                System.out.println(columnNames2);
                System.out.println();
            }
        } catch (Exception e) {
            // TODO: handle SQLException
            e.printStackTrace();
        }
    }


    /**
     * 获取数据库一些信息
     */
    public void getDataBaseInformations()
    {
        try
        {
            conn = getConnection();
            statement = conn.createStatement();
            DatabaseMetaData dbMetaData = getMetaData();
            System.out.println("下面是数据库的相关信息");
            System.out.println("URL:" + dbMetaData.getURL() + ";");
            System.out.println("UserName:" + dbMetaData.getUserName() + ";");
            System.out.println("isReadOnly:" + dbMetaData.isReadOnly() + ";");
            System.out.println("DatabaseProductName:" + dbMetaData.getDatabaseProductName() + ";");
            System.out.println("DatabaseProductVersion:" + dbMetaData.getDatabaseProductVersion() + ";");
            System.out.println("DriverName:" + dbMetaData.getDriverName() + ";");
            System.out.println("DriverVersion:" + dbMetaData.getDriverVersion());
        } catch (SQLException e)
        {
            // TODO: handle SQLException
            e.printStackTrace();
        }
    }
    /**
     * 获得数据库中所有方案名称
     */
    public void getAllSchemas()
    {
        try
        {
            DatabaseMetaData dbMetaData = getMetaData();
            ResultSet rs = dbMetaData.getSchemas();
            while (rs.next())
            {
                String tableSchem = rs.getString("TABLE_SCHEM");
                System.out.println("tableSchem为：" +tableSchem);
            }
        } catch (SQLException e)
        {
            // TODO: handle SQLException
            e.printStackTrace();
        }
    }

    /**
     * 返回 连接的元数据
     * @return
     * @throws SQLException
     */
    private DatabaseMetaData getMetaData() throws SQLException {
        return getConnection().getMetaData();
    }


    /**
     * 获得表或视图中的所有列信息  
     */
    public void getTableColumns(String tableName)
    {
        try
        {
            DatabaseMetaData dbMetaData = getMetaData();
            ResultSet rs = dbMetaData.getColumns(null, dbMetaData.getDatabaseProductName(), tableName, "%");
            while (rs.next())
            {
                // table catalog (may be null)   
                String tableCat = rs.getString("TABLE_CAT");
                // table schema (may be null)   
                String tableSchemaName = rs.getString("TABLE_SCHEM");
                // table name   
                String tableName_ = rs.getString("TABLE_NAME");
                // column name   
                String columnName = rs.getString("COLUMN_NAME");
                // SQL type from java.sql.Types   
                int dataType = rs.getInt("DATA_TYPE");
                // Data source dependent type name, for a UDT the type name is   
                // fully qualified   
                String dataTypeName = rs.getString("TYPE_NAME");
                // table schema (may be null)   
                int columnSize = rs.getInt("COLUMN_SIZE");
                // the number of fractional digits. Null is returned for data   
                // types where DECIMAL_DIGITS is not applicable.   
                int decimalDigits = rs.getInt("DECIMAL_DIGITS");
                // Radix (typically either 10 or 2)   
                int numPrecRadix = rs.getInt("NUM_PREC_RADIX");
                // is NULL allowed.   
                int nullAble = rs.getInt("NULLABLE");
                // comment describing column (may be null)   
                String remarks = rs.getString("REMARKS");
                // default value for the column, which should be interpreted as   
                // a string when the value is enclosed in single quotes (may be   
                // null)   
                String columnDef = rs.getString("COLUMN_DEF");
                //                 
                int sqlDataType = rs.getInt("SQL_DATA_TYPE");
                //                 
                int sqlDatetimeSub = rs.getInt("SQL_DATETIME_SUB");
                // for char types the maximum number of bytes in the column   
                int charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");
                // index of column in table (starting at 1)   
                int ordinalPosition = rs.getInt("ORDINAL_POSITION");
                // ISO rules are used to determine the nullability for a column.   
                // YES --- if the parameter can include NULLs;   
                // NO --- if the parameter cannot include NULLs   
                // empty string --- if the nullability for the parameter is   
                // unknown   
                String isNullAble = rs.getString("IS_NULLABLE");
                // Indicates whether this column is auto incremented   
                // YES --- if the column is auto incremented   
                // NO --- if the column is not auto incremented   
                // empty string --- if it cannot be determined whether the   
                // column is auto incremented parameter is unknown   
                String isAutoincrement = rs.getString("IS_AUTOINCREMENT");
                System.out.println("tableCat : " + tableCat +
                        " - " + "tableSchemaName : " + tableSchemaName +
                        " - " + "tableName_ : " + tableName_ +
                        " - " + "columnName : " +columnName +
                        " - " + "dataType : " + dataType +
                        " - " +"dataTypeName : " + dataTypeName +
                        " - " + "columnSize :" + columnSize +
                        " - " + "decimalDigits : " +  decimalDigits +
                        " numPrecRadix : " + "-" + numPrecRadix +
                        " nullAble : " + "-" + nullAble +
                        " remarks : " + "-" + remarks +
                        " columnDef : " + "-" + columnDef +
                        " sqlDataType : " + "-" + sqlDataType +
                        " sqlDatetimeSub : " + "-" + sqlDatetimeSub +
                        " charOctetLength : " + "-" + charOctetLength +
                        " ordinalPosition : " + "-" + ordinalPosition +
                        " isNullAble : " + "-" + isNullAble +
                        " isAutoincrement : " + "-" + isAutoincrement + "-");
            }
        } catch (SQLException e)
        {
            // TODO: handle SQLException   
            e.printStackTrace();
        }
    }

    /**
     * 获得该用户下面的所有视图
     */
    public void getAllViewList(String schemaName)
    {
        try
        {
            DatabaseMetaData dbMetaData = getMetaData();
            String[] types =
                    { "VIEW" };
            ResultSet rs = dbMetaData.getTables(null, schemaName, "%", types);
            System.out.println("下面输出该用户下面的所有视图");
            while (rs.next())
            {
                String viewName = rs.getString("TABLE_NAME");
                String viewType = rs.getString("TABLE_TYPE");
                // 表上的解释性评论
                String remarks = rs.getString("REMARKS");
                System.out.println("这是视图相关信息： " + viewName + "  -  "   + viewType + " -  " + remarks);
            }
        } catch (SQLException e)
        {
            // TODO: handle SQLException
            e.printStackTrace();
        }
    }

    /**
     * 获得一个表的主键信息
     */
    public void getAllPrimaryKeys(String schemaName, String tableName)
    {
        try
        {
            DatabaseMetaData dbMetaData = getMetaData();
            ResultSet rs = dbMetaData.getPrimaryKeys(null, schemaName, tableName);
            if (rs.next() == false) {
                System.out.println("没有主键信息");
            }
            while (rs.next())
            {
                // column name
                String columnName = rs.getString("COLUMN_NAME");
                // sequence number within primary key( a value of 1 represents
                // the first column of the primary key, a value of 2 would
                // represent the second column within the primary key).
                short keySeq = rs.getShort("KEY_SEQ");
                // primary key name (may be null)
                String pkName = rs.getString("PK_NAME");
                System.out.println("字段名： " + columnName + " -主键内序列号： " + keySeq + " - 主键名称： " + pkName);
            }
        } catch (SQLException e)
        {
            // TODO: handle SQLException
            e.printStackTrace();
        }
    }

    /**
     * 获得一个表的外键信息
     */
    public void getAllExportedKeys(String schemaName, String tableName)
    {
        try
        {
            DatabaseMetaData dbMetaData = getMetaData();
            ResultSet rs = dbMetaData.getExportedKeys(null, schemaName, tableName);
            if (rs.next()==false) {
                System.out.println("该表没有外键信息");
            }
            while (rs.next())
            {
                // primary key table catalog (may be null)
                String pkTableCat = rs.getString("PKTABLE_CAT");
                // primary key table schema (may be null)
                String pkTableSchem = rs.getString("PKTABLE_SCHEM");
                // primary key table name
                String pkTableName = rs.getString("PKTABLE_NAME");
                // primary key column name
                String pkColumnName = rs.getString("PKCOLUMN_NAME");
                // foreign key table catalog (may be null) being exported (may
                // be null)
                String fkTableCat = rs.getString("FKTABLE_CAT");
                // foreign key table schema (may be null) being exported (may be
                // null)
                String fkTableSchem = rs.getString("FKTABLE_SCHEM");
                // foreign key table name being exported
                String fkTableName = rs.getString("FKTABLE_NAME");
                // foreign key column name being exported
                String fkColumnName = rs.getString("FKCOLUMN_NAME");
                // sequence number within foreign key( a value of 1 represents
                // the first column of the foreign key, a value of 2 would
                // represent the second column within the foreign key).
                short keySeq = rs.getShort("KEY_SEQ");
                // What happens to foreign key when primary is updated:
                // importedNoAction - do not allow update of primary key if it
                // has been imported
                // importedKeyCascade - change imported key to agree with
                // primary key update
                // importedKeySetNull - change imported key to NULL if its
                // primary key has been updated
                // importedKeySetDefault - change imported key to default values
                // if its primary key has been updated
                // importedKeyRestrict - same as importedKeyNoAction (for ODBC
                // 2.x compatibility)
                short updateRule = rs.getShort("UPDATE_RULE");
                short delRule = rs.getShort("DELETE_RULE");
                // foreign key name (may be null)
                String fkName = rs.getString("FK_NAME");
                // primary key name (may be null)
                String pkName = rs.getString("PK_NAME");
                short deferRability = rs.getShort("DEFERRABILITY");
                System.out.println(pkTableCat + "-" + pkTableSchem + "-" + pkTableName + "-" + pkColumnName + "-"
                        + fkTableCat + "-" + fkTableSchem + "-" + fkTableName + "-" + fkColumnName + "-" + keySeq + "-"
                        + updateRule + "-" + delRule + "-" + fkName + "-" + pkName + "-" + deferRability);
            }
        } catch (SQLException e)
        {
            // TODO: handle SQLException
            e.printStackTrace();
        }
    }

    /**
     * 查看表结构
     */
    public ResultSet describeTable(String tableName) {
        try {
            conn = getConnection();
            statement = conn.createStatement();
            String sql = " describe " + tableName;
            rs = statement.executeQuery(sql);
            String createSQL = null;
            System.out.println("查看表结构");
            while (rs.next()) {
                BufferedReader br = new BufferedReader(new InputStreamReader(rs.getAsciiStream(rs.getMetaData().getColumnCount()), new String("GBK")));
                while ((createSQL = br.readLine()) != null) {
                    System.out.println(createSQL);
                }
            }
        } catch (Exception e) {
            //：如果你的SQL 语句是诸如update,insert的更新语句，应该用statement的execute()方法，
            // 如果用的是statement的executeQuery()就会出现上诉问题
            log.error("查询数据异常:" + e.getMessage());
        }
        return rs;
    }


}
